/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.Jzdj;
import me.zhengjie.business.domain.Whfa;
import me.zhengjie.business.domain.vo.FwjhVo;
import me.zhengjie.business.domain.vo.KB03;
import me.zhengjie.business.domain.vo.KB22;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-07-18
**/
public interface WhfaRepository extends JpaRepository<Whfa, String>, JpaSpecificationExecutor<Whfa> {

    /**
     * 根据月份查询照护方案
     * @return /
     */
    @Query(value = "select * from bis_whfa where bis_whfa_zhsq_id=?1 and bis_whfa_hwrq like ?2||'%'",nativeQuery = true)
    List<Whfa> findWhfaByMonth(Long zhid, String month);

    /**
     * 根据月份查询照护方案
     * @return /
     */
    @Query(value = "select count(1) from bis_whfa where bis_whfa_zhsq_id=?1 and bis_whfa_hwrq like ?2||'%' and bis_whfa_fwlx=?3",nativeQuery = true)
    Integer checkFaCnt(Long zhid, String month, String fwlx);

    Whfa queryByBisWhfaZhsqIdAndBisWhfaHwrq(Long zhid, String whrq);



    /**
     * 查询居家服务计划，用于分页
     * @param akb020
     * @param blurry
     * @param date
     * @param pageable
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.FwjhVo( " +
            "       r3.bisGsglQymc," +
            "       r3.bisGsglYljgdm," +
            "       r2.bisZhsqSnryXm," +
            "       r2.bisZhsqSnrySfz," +
            "       r1.bisWhfaHwrq," +
            "       r1.bisWhfaStartTime," +
            "       r1.bisWhfaEndTime," +
            "       r1.bisWhfaZt," +
            "       r1.bisWhfaFwlx," +
            "       r1.bisWhfaZhsqId," +
            "       r1.bisWhfaId," +
            "       r2.bisZhsqLxdh," +
            "       r2.bisZhsqJhrxm," +
            "       r2.bisZhsqJhrdh," +
            "       r2.bisZhsqHldz," +
            "       r2.bisZhsqHldzXxdz, " +
            "       r4.bisFpryRyglXm, " +
            "       r5.bisRyglLxdh) " +
            "       from Whfa r1 " +
            "       inner join Zhsq r2 on r1.bisWhfaZhsqId = r2.bisZhsqId " +
            "       inner join Hljg r3 on r2.bisZhsqHljgBm = r3.bisGsglYljgdm " +
            "       left join Fpry r4 on r1.bisWhfaZhsqId = r4.bisFpryZhsqId and r4.bisFpryZt='0' and r4.bisFpryRylx='RYLX03' " +
            "       left join Rygl r5 on r4.bisFpryRyglId = r5.bisRyglUserId " +
            "       where 1=1 " +
            "       and (?1 IS NULL OR ?1 = '' OR r3.bisGsglYljgdm = ?1 OR r3.bisGsglQymc like '%'||?1||'%')  " +
            "       and (?2 IS NULL OR ?2 = '' OR r2.bisZhsqSnryXm like '%'||?2||'%' OR r2.bisZhsqSnrySfz like '%'||?2||'%' ) " +
            "       and (?3 IS NULL OR ?3 = '' OR r1.bisWhfaHwrq = ?3 ) " +
            "       and (?4 IS NULL OR ?4 = '' OR r3.sbgs.bisGsglId = ?4 ) ",
            countQuery = "select count(*) " +
                "       from Whfa r1 " +
                "       inner join Zhsq r2 on r1.bisWhfaZhsqId = r2.bisZhsqId " +
                "       inner join Hljg r3 on r2.bisZhsqHljgBm = r3.bisGsglYljgdm " +
                "       left join Fpry r4 on r1.bisWhfaZhsqId = r4.bisFpryZhsqId and r4.bisFpryZt='0' and r4.bisFpryRylx='RYLX03' " +
                "       left join Rygl r5 on r4.bisFpryRyglId = r5.bisRyglUserId " +
                "       where 1=1 " +
                "       and (?1 IS NULL OR ?1 = '' OR r3.bisGsglYljgdm = ?1 OR r3.bisGsglQymc like '%'||?1||'%')  " +
                "       and (?2 IS NULL OR ?2 = '' OR r2.bisZhsqSnryXm like '%'||?2||'%' OR r2.bisZhsqSnrySfz like '%'||?2||'%' ) " +
                "       and (?3 IS NULL OR ?3 = '' OR r1.bisWhfaHwrq = ?3 ) " +
                "       and (?4 IS NULL OR ?4 = '' OR r3.sbgs.bisGsglId = ?4 ) ")
    Page<FwjhVo> queryFwjhVo(String akb020, String blurry, String date, String sbgs, Pageable pageable);


    /**
     * 查询居家服务计划，用于导出
     * @param akb020
     * @param blurry
     * @param date
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.FwjhVo( " +
            "       r3.bisGsglQymc," +
            "       r3.bisGsglYljgdm," +
            "       r2.bisZhsqSnryXm," +
            "       r2.bisZhsqSnrySfz," +
            "       r1.bisWhfaHwrq," +
            "       r1.bisWhfaStartTime," +
            "       r1.bisWhfaEndTime," +
            "       r1.bisWhfaZt," +
            "       r1.bisWhfaFwlx," +
            "       r1.bisWhfaZhsqId," +
            "       r1.bisWhfaId," +
            "       r2.bisZhsqLxdh," +
            "       r2.bisZhsqJhrxm," +
            "       r2.bisZhsqJhrdh," +
            "       r2.bisZhsqHldz," +
            "       r2.bisZhsqHldzXxdz, " +
            "       r4.bisFpryRyglXm, " +
            "       r5.bisRyglLxdh) " +
            "       from Whfa r1 " +
            "       inner join Zhsq r2 on r1.bisWhfaZhsqId = r2.bisZhsqId " +
            "       inner join Hljg r3 on r2.bisZhsqHljgBm = r3.bisGsglYljgdm " +
            "       left join Fpry r4 on r1.bisWhfaZhsqId = r4.bisFpryZhsqId and r4.bisFpryZt='0' and r4.bisFpryRylx='RYLX03' " +
            "       left join Rygl r5 on r4.bisFpryRyglId = r5.bisRyglUserId " +
            "       where 1=1 " +
            "       and (?1 IS NULL OR ?1 = '' OR r3.bisGsglYljgdm = ?1 OR r3.bisGsglQymc like '%'||?1||'%')  " +
            "       and (?2 IS NULL OR ?2 = '' OR r2.bisZhsqSnryXm like '%'||?2||'%' OR r2.bisZhsqSnrySfz like '%'||?2||'%' ) " +
            "       and (?3 IS NULL OR ?3 = '' OR r1.bisWhfaHwrq = ?3 ) " +
            "       and (?4 IS NULL OR ?4 = '' OR r3.sbgs.bisGsglId = ?4 ) ")
    List<FwjhVo> queryAllFwjhVo(String akb020, String blurry, String date, String sbgs);
}